package com.wolfking.converter.service;

import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.wolfking.converter.config.DynamicDataSourceContext;
import com.wolfking.converter.entity.*;
import com.wolfking.converter.exception.ParamVerifyException;
import com.wolfking.converter.transform.FieldTransform;
import com.wolfking.converter.util.FieldConverterUtil;
import com.wolfking.converter.web.DefaultResponseConverter;
import com.wolfking.jeesite.freemarker.TemplateRenderService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.MissingServletRequestParameterException;

import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
public class SqlConverterExecuteService {

    @Autowired
    private JdbcTemplateService jdbcTemplateService;

    @Autowired
    private SqlConfigInterface sqlConfigInterface;

    @Autowired
    private DefaultResponseConverter defaultResponseConverter;

    @Autowired
    private DynamicDataSourceContext dataSourceContext;

    @Autowired
    private TemplateRenderService templateRenderService;

    private Gson gson = new GsonBuilder().setPrettyPrinting().disableHtmlEscaping().setDateFormat("yyyy-MM-dd HH:mm:ss").create();


    private KvObject<SqlDefine, List<SqlDefineParam>> checkParameter(SqlDefine sqlDefine, List<SqlDefineParam> defineParams,
                                                                     Map<String, Object> params, boolean web, boolean list) throws Exception {
        if (Objects.isNull(sqlDefine)) {
            String key = (String) params.get("key");
            if (StringUtils.isBlank(key)) {
                throw new MissingServletRequestParameterException("key", "String");
            }
            sqlDefine = sqlConfigInterface.querySqlDefineByKey(key);
            if (Objects.isNull(sqlDefine)) {
                throw new ParamVerifyException(key + "不存在配置");
            }
        }
        if (Objects.isNull(defineParams)) {
            defineParams = sqlConfigInterface.querySqlDefineParam(sqlDefine.getDefineKey());
        }
        Map<String, Object> sqlBindParam = Maps.newHashMap();
        //页面空参数查询
        if (!sqlDefine.getAllowEmptyParamQuery()) {
            Map<String, Object> checkParam = Maps.newHashMap(params);
            checkParam.remove("key");
            if (!checkParam.isEmpty()) {
                for (SqlDefineParam sqlDefineParam : defineParams) {
                    if (sqlDefineParam.getPageNum() || sqlDefineParam.getPageSize()) {
                        checkParam.remove(sqlDefineParam.getParamName());
                    }
                }
            }
            if (checkParam.isEmpty()) {
                throw new ParamVerifyException("所有参数为空，请输入参数");
            }
        }

        //参数转换器和默认值赋值
        for (SqlDefineParam defineParam : defineParams) {
            if (params.containsKey(defineParam.getParamName())) {
                defineParam.setDefaultValue(params.get(defineParam.getParamName()).toString());
                Object value = convertObject(defineParam, params.get(defineParam.getParamName()));
                defineParam.setValue(value);
                sqlBindParam.put(defineParam.getParamName(), value);
            } else {
                if (defineParam.getRequired()) {
                    throw new MissingServletRequestParameterException(defineParam.getParamName(), defineParam.getParamType());
                } else {
                    String defaultValue = defineParam.getDefaultValue();
                    //非web页面请求，添加默认值
                    if (StringUtils.isNotBlank(defaultValue) && !web) {
                        String renderValue = templateRenderService.renderTemplate(defaultValue);
                        defineParam.setDefaultValue(renderValue);
                        Object value = convertObject(defineParam, renderValue);
                        defineParam.setValue(value);
                        sqlBindParam.put(defineParam.getParamName(), value);
                    }
                }
            }
        }
        //参数统一校验
        if (StringUtils.isNotBlank(sqlDefine.getParamVerify())) {
            String verifyMessage;
            try {
                verifyMessage = templateRenderService.renderTemplate(sqlDefine.getParamVerify(), ImmutableMap.of("params", sqlBindParam));
            } catch (Exception e) {
                verifyMessage = "参数脚本校验出错，详情是\n" + ExceptionUtils.getStackTrace(e);
            }
            if (StringUtils.isNotBlank(verifyMessage)) {
                throw new ParamVerifyException(verifyMessage.trim());
            }
        }
        if (list) {
            for (SqlDefineParam sqlDefineParam : defineParams) {
                if (sqlDefineParam.getPageSize() != null && sqlDefineParam.getPageSize()) {
                    sqlDefineParam.setValue(100000000);
                }
                if (sqlDefineParam.getPageNum() != null && sqlDefineParam.getPageNum()) {
                    sqlDefineParam.setValue(0);
                }
            }
        } else if (StringUtils.isNotBlank(sqlDefine.getCountSqlBase64Decode())) {
            SqlDefineParam pageNumParam = getPageNumParam(defineParams);
            SqlDefineParam pageSizeParam = getPageSizeParam(defineParams);
            int pageNum = getPageNum(defineParams);
            int pageSize = getPageSize(defineParams);
            pageNumParam.setDefaultValue(pageNum + "");
            pageSizeParam.setDefaultValue(pageSize + "");
            pageNum = assemblyNewPageNum(pageNum, pageSize);
            pageNumParam.setValue(pageNum);
        }

        return new KvObject<>(sqlDefine, defineParams);
    }


    public ExecuteResult executeResult(Map<String, Object> map, boolean list) throws Exception {
        String key = map.get("key").toString();
        SqlDefine sqlDefine = sqlConfigInterface.querySqlDefineByKey(key);
        List<SqlDefineParam> sqlDefineParams = sqlConfigInterface.querySqlDefineParam(key);
        return executeResult(sqlDefine, sqlDefineParams, map, list);
    }

    public ExecuteResult executeResult(SqlDefine sqlDefine, List<SqlDefineParam> sqlDefineParams, Map<String, Object> map, boolean list) throws Exception {
        checkParameter(sqlDefine, sqlDefineParams, map, true, list);
        List<SqlFieldTransform> transforms = sqlConfigInterface.getSqlFiledTransformByKey(sqlDefine.getDefineKey());
        KvObject<String, Map<String, Object>> entry = assemblySql(sqlDefine, sqlDefineParams, false);
        String key = Arrays.stream(entry.getKey().split("\n")).map(String::trim)
                .filter(StringUtils::isNotBlank).collect(Collectors.joining("\n"));
        log.info("\n {} queryList sql is {},\nparam is {},\ntransforms is {}",
                sqlDefine.getDataSource(), key, gson.toJson(entry.getValue()), transforms);
        ExecuteResult executeResult = sqlDefine.getUnderLineToCamel() ?
                jdbcTemplateService.queryArrayUnderLineToCamel2ExecuteResult(sqlDefine.getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue())
                : jdbcTemplateService.queryArray2ExecuteResult(sqlDefine.getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue());
        if (StringUtils.isNotBlank(sqlDefine.getCountSqlBase64())) {
            entry = assemblySql(sqlDefine, sqlDefineParams, true);
            int count = jdbcTemplateService.queryCount(sqlDefine.getDataSource(), entry.getKey(), entry.getValue());
            //处理这两个值
            int pageNum = getPageNum(sqlDefineParams);
            int pageSize = getPageSize(sqlDefineParams);
            executeResult.setPage((Map<String, Object>) defaultResponseConverter.convertPage(
                    new ConverterPage(pageNum, pageSize, count, new JsonArray())));
        }
        return executeResult;
    }


    public KvObject<SqlDefine, ?> executeOne(Map<String, Object> map) throws Exception {
        KvObject<SqlDefine, List<SqlDefineParam>> kvObject = checkParameter(null, null, map, false, false);
        List<SqlFieldTransform> transforms = sqlConfigInterface.getSqlFiledTransformByKey(kvObject.getKey().getDefineKey());
        KvObject<String, Map<String, Object>> entry = this.assemblySql(kvObject.getKey(), kvObject.getValue(), false);
        log.info("\nqueryOne sql is {},\nparam is {},\ntransforms is {}", kvObject.getKey(), kvObject.getValue(), transforms);
        JsonObject jsonObject = kvObject.getKey().getUnderLineToCamel() ?
                jdbcTemplateService.queryObjectUnderLineToCamel(kvObject.getKey().getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue()) :
                jdbcTemplateService.queryObject(kvObject.getKey().getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue());
        return new KvObject<>(kvObject.getKey(), jsonObject);
    }

    public KvObject<SqlDefine, ?> executeList(Map<String, Object> map) throws Exception {
        KvObject<SqlDefine, List<SqlDefineParam>> kvObject = checkParameter(null, null, map, false, false);
        List<SqlFieldTransform> transforms = sqlConfigInterface.getSqlFiledTransformByKey(kvObject.getKey().getDefineKey());
        KvObject<String, Map<String, Object>> entry = this.assemblySql(kvObject.getKey(), kvObject.getValue(), false);
        JsonArray array = kvObject.getKey().getUnderLineToCamel() ?
                jdbcTemplateService.queryArrayUnderLineToCamel(kvObject.getKey().getDataSource(), entry.getKey(),
                        assemblyFieldTransform(transforms), entry.getValue())
                : jdbcTemplateService.queryArray(kvObject.getKey().getDataSource(), entry.getKey(),
                assemblyFieldTransform(transforms), entry.getValue());
        return new KvObject<>(kvObject.getKey(), array);
    }

    public KvObject<SqlDefine, ?> executePage(Map<String, Object> bodyMap) throws Exception {
        KvObject<SqlDefine, List<SqlDefineParam>> kvObject = checkParameter(null, null, bodyMap, false, false);
        List<SqlFieldTransform> transforms = sqlConfigInterface.getSqlFiledTransformByKey(kvObject.getKey().getDefineKey());
        KvObject<String, Map<String, Object>> entry = this.assemblySql(kvObject.getKey(), kvObject.getValue(), false);
        log.info("\nqueryPage sql is {},\nparam is {},\ntransforms is {}", entry.getKey(), entry.getValue(), transforms);
        JsonArray array = !kvObject.getKey().getUnderLineToCamel() ?
                jdbcTemplateService.queryArrayUnderLineToCamel(kvObject.getKey().getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue()) :
                jdbcTemplateService.queryArray(kvObject.getKey().getDataSource(), entry.getKey(), assemblyFieldTransform(transforms), entry.getValue());
        entry = this.assemblySql(kvObject.getKey(), kvObject.getValue(), true);
        int count = jdbcTemplateService.queryCount(kvObject.getKey().getDataSource(), entry.getKey(), entry.getValue());
        ConverterPage converterPage = new ConverterPage();
        //处理这两个值
        int pageNum = getPageNum(kvObject.getValue());
        int pageSize = getPageSize(kvObject.getValue());
        converterPage.setPageNum(pageNum);
        converterPage.setPageSize(pageSize);
        converterPage.setCount(count);
        converterPage.setArray(array);
        return new KvObject<>(kvObject.getKey(), converterPage);
    }

    private Map<String, FieldTransform<?>> assemblyFieldTransform(List<SqlFieldTransform> transforms) throws Exception {
        Map<String, FieldTransform<?>> map = Maps.newHashMap();
        if (!CollectionUtils.isEmpty(transforms)) {
            for (SqlFieldTransform sqlFieldTransform : transforms) {
                try {
                    FieldTransform<?> transform = null;
                    try {
                        if (StringUtils.isNotBlank(sqlFieldTransform.getConstructArg())) {
                            transform = (FieldTransform<?>) Class.forName(sqlFieldTransform.getTransform()).getDeclaredConstructor(String.class)
                                    .newInstance(sqlFieldTransform.getConstructArg());
                        } else {
                            transform = (FieldTransform<?>) Class.forName(sqlFieldTransform.getTransform()).newInstance();
                        }
                    } catch (Exception ee) {
                        try {
                            transform = (FieldTransform<?>) Class.forName(sqlFieldTransform.getTransform()).newInstance();
                        } catch (Exception eee) {
                            throw ee;
                        }
                    }
                    if (Objects.nonNull(transform)) {
                        map.put(sqlFieldTransform.getFieldName(), transform);
                    }
                } catch (Exception e) {
                    log.warn("", e);

                }
            }
        }
        return map;
    }

    private KvObject<String, Map<String, Object>> assemblySql(
            SqlDefine sqlDefine, List<SqlDefineParam> defineParams, boolean count) throws Exception {
        Map<String, Object> sqlBindParam = Maps.newHashMap();
        String sql = count ? sqlDefine.getCountSqlBase64Decode() : sqlDefine.getSqlBase64Decode();
        for (SqlDefineParam defineParam : defineParams) {
            if (Objects.nonNull(defineParam.getValue())) {
                if (count && (defineParam.getPageSize() || defineParam.getPageNum())) {
                    continue;
                } else {
                    sqlBindParam.put(defineParam.getParamName(), defineParam.getValue());
                }
            }
        }
        for (SqlDefineParam defineParam : defineParams) {
            if (Objects.nonNull(defineParam.getValue()) && StringUtils.isNotBlank(defineParam.getExpression())) {
                sql = sql.replace(String.format("{{%s}}", defineParam.getParamName()),
                        templateRenderService.renderTemplate(defineParam.getExpressionBase64Decode(), ImmutableMap.of("params", sqlBindParam)));
            } else {
                sql = sql.replace(String.format("{{%s}}", defineParam.getParamName()), "\n");
            }
        }
        sql = templateRenderService.renderTemplate(sql, ImmutableMap.of("params", sqlBindParam));
        return new KvObject(sql, sqlBindParam);
    }

    private Object convertObject(SqlDefineParam sqlDefineParam, Object value) throws Exception {

        if (StringUtils.isNotBlank(sqlDefineParam.getConverter())) {
            //先按照基础模型转换，再按照配置的模型转换
            for (FieldTransform<?> fieldTransform : FieldConverterUtil.FIELD_TRANSFORMS) {
                if (fieldTransform.canTransform(Class.forName(sqlDefineParam.getParamType()))) {
                    Object transform;
                    try {
                        transform = fieldTransform.transform(Class.forName(sqlDefineParam.getParamType()), value);
                    } catch (Exception e) {
                        log.warn("基础类型转换失败", e);
                        transform = value;
                    }
                    return getFieldTransform(sqlDefineParam).transform(Class.forName(sqlDefineParam.getParamType()), transform);
                }
            }
        } else if (value instanceof Collection) {
            List<Object> arrayParam = Lists.newArrayList();
            for (Object obj : (Collection<?>) value) {
                for (FieldTransform<?> fieldTransform : FieldConverterUtil.FIELD_TRANSFORMS) {
                    if (StringUtils.isNotBlank(sqlDefineParam.getFormat())) {
                        fieldTransform = fieldTransform.getClass().getDeclaredConstructor(String.class)
                                .newInstance(sqlDefineParam.getFormat());
                    }
                    if (fieldTransform.canTransform(Class.forName(sqlDefineParam.getParamType()))) {
                        arrayParam.add(fieldTransform.transform(Class.forName(sqlDefineParam.getParamType()), obj));
                    }
                }
            }
            return !arrayParam.isEmpty() ? arrayParam : value;
        } else {
            for (FieldTransform<?> fieldTransform : FieldConverterUtil.FIELD_TRANSFORMS) {
                if (StringUtils.isNotBlank(sqlDefineParam.getFormat())) {
                    try {
                        fieldTransform = fieldTransform.getClass().getDeclaredConstructor(String.class)
                                .newInstance(sqlDefineParam.getFormat());
                    } catch (Exception e) {
                        continue;
                    }
                }
                if (fieldTransform.canTransform(Class.forName(sqlDefineParam.getParamType()))) {
                    return fieldTransform.transform(Class.forName(sqlDefineParam.getParamType()), value);
                }
            }
        }
        return null;
    }


    private FieldTransform getFieldTransform(SqlDefineParam sqlDefineParam) throws Exception {
        FieldTransform<?> fieldTransform;
        if (StringUtils.isNotBlank(sqlDefineParam.getFormat())) {
            fieldTransform = (FieldTransform<?>) Class.forName(sqlDefineParam.getConverter())
                    .getDeclaredConstructor(String.class).newInstance(sqlDefineParam.getFormat());
        } else {
            fieldTransform = (FieldTransform<?>) Class.forName(sqlDefineParam.getConverter()).newInstance();
        }
        return fieldTransform;
    }

    private int getPageNum(List<SqlDefineParam> params) {
        for (SqlDefineParam defineParam : params) {
            if (defineParam.getPageNum() != null && defineParam.getPageNum()) {
                if (Objects.nonNull(defineParam.getDefaultValue())) {
                    return Integer.parseInt(defineParam.getDefaultValue().toString());
                }
            }
        }
        return 0;
    }

    private int getPageSize(List<SqlDefineParam> params) {
        for (SqlDefineParam defineParam : params) {
            if (defineParam.getPageSize() != null && defineParam.getPageSize()) {
                if (Objects.nonNull(defineParam.getDefaultValue())) {
                    return Integer.parseInt(defineParam.getDefaultValue().toString());
                }
            }
        }
        return 10;
    }

    private SqlDefineParam getPageNumParam(List<SqlDefineParam> params) {
        for (SqlDefineParam defineParam : params) {
            if (defineParam.getPageNum() != null && defineParam.getPageNum()) {
                return defineParam;
            }
        }
        throw new RuntimeException("config not exist the page num param");
    }

    private SqlDefineParam getPageSizeParam(List<SqlDefineParam> params) {
        for (SqlDefineParam defineParam : params) {
            if (defineParam.getPageSize() != null && defineParam.getPageSize()) {
                return defineParam;
            }
        }
        throw new RuntimeException("config not exist the page size param");
    }


    private int assemblyNewPageNum(int pageNum, int pageSize) {
        return (pageNum - 1) * pageSize;
    }


}
